﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using System.Data.SqlClient;
using System.Data;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using bdxyit.com.RecruitmentAppSys.Models;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：SystemInfoModels
*系统时间：3/25/2012 3:37:48 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:37:48 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class SystemInfoDAL : ISystemInfoDAL
    {
        private const string SQL_SELECT_SYSTEMINFO_BY_TYPE = " select ID,SysItem,Type,SysID,Other from SystemInfos "
                                                           + " where Type=@Type";

        private const string SQL_DELETE_SYSTEMINFO_TYPE = " delete from SystemInfos "
                                                        + " where Type=@Type";

        private const string SQL_UPDATE_SYSTEMINFO_TYPE = " update SystemInfos set "
                                                        + " SysItem=@SysItem,"
                                                        + " Type=@Type,"
                                                        + " SysID=@SysID,"
                                                        + " Other=@Other"
                                                        + " where Type=@Type";


        private const string SQL_INSERT_SYSTEMINFO = " insert into SystemInfos("
                                                   + " ID,SysItem,Type,SysID,Other)"
                                                   + "  values ("
                                                   + " @ID,@SysItem,@Type,@SysID,@Other)";

        private const string FIELD_ID = "@ID";
        private const string FIELD_SysItem = "@SysItem";
        private const string FIELD_Type = "@Type";
        private const string FIELD_SysID = "@SysID";
        private const string FIELD_Other = "@Other";



        /// <summary>
        /// 根据type查询列表
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public List<SystemInfo> SelectSystemInfoByCode(string type)
        {
            List<SystemInfo> systemInfo = new List<SystemInfo>();
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_Type, SqlDbType.VarChar, 50);
            parm.Value = type;

            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_SYSTEMINFO_BY_TYPE, parm))
            {

                while (rdr.Read())
                {
                    SystemInfo fi = new SystemInfo();
                    if (rdr.HasRows)
                    {
                        if (rdr["ID"] != null && rdr["ID"].ToString() != "")
                        {
                            fi.id = int.Parse(rdr["ID"].ToString());
                        }
                        if (rdr["SysItem"] != null && rdr["SysItem"].ToString() != "")
                        {
                            fi.SysItem = rdr["SysItem"].ToString();
                        }
                        if (rdr["Type"] != null && rdr["Type"].ToString() != "")
                        {
                            fi.Type = rdr["Type"].ToString();
                        }
                        if (rdr["SysID"] != null && rdr["SysID"].ToString() != "")
                        {
                            fi.SysID = rdr["SysID"].ToString();
                        }
                        if (rdr["Other"] != null && rdr["Other"].ToString() != "")
                        {
                            fi.Other = rdr["Other"].ToString();
                        }
                    }
                    systemInfo.Add(fi);
                }
            }
            return systemInfo;
        }


        /// <summary>
        /// 根据type删除记录
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool DeleteSystemInfoByCode(string type)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_Type, SqlDbType.VarChar, 50);
            parm.Value = type;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_SYSTEMINFO_TYPE;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <param name="sif"></param>
        /// <returns></returns>
        public bool InsertSystemInfo(SystemInfo sif)
        {
            bool flag = false;

            SqlParameter[] UserInfoParms = GetSystemInfoParameters();
            SqlCommand cmd = new SqlCommand();
            UserInfoParms[0].Value = sif.id;
            UserInfoParms[1].Value = sif.SysItem;
            UserInfoParms[2].Value = sif.Type;
            UserInfoParms[3].Value = sif.SysID;
            UserInfoParms[4].Value = sif.Other;
       
            foreach (SqlParameter parm in UserInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_SYSTEMINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 更新一列
        /// </summary>
        /// <param name="sif"></param>
        /// <returns></returns>
        public bool UpdateSystemInfo(SystemInfo sif)
        {
            bool flag = false;
            SqlParameter[] SystemInfoParms = GetSystemInfoParameters();
            SqlCommand cmd = new SqlCommand();
            SystemInfoParms[0].Value = sif.id;
            SystemInfoParms[1].Value = sif.SysItem;
            SystemInfoParms[2].Value = sif.Type;
            SystemInfoParms[3].Value = sif.SysID;
            SystemInfoParms[4].Value = sif.Other;

            foreach (SqlParameter parm in SystemInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_SYSTEMINFO_TYPE;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }


        /// <summary>
        /// 获取列参数
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] GetSystemInfoParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
	            	new SqlParameter(FIELD_ID, SqlDbType.Int,4),
					new SqlParameter(FIELD_SysItem, SqlDbType.VarChar,50),
					new SqlParameter(FIELD_Type, SqlDbType.VarChar,20),
					new SqlParameter(FIELD_SysID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_Other, SqlDbType.VarChar,100)};
            return parms;
        }




    }
}